package com.hxwm.hwdbtool.controller;

import com.hxwm.hwdbtool.controller.dialog.BaseDBConnectDialog;
import com.hxwm.hwdbtool.core.DatabaseConnectionListener;
import com.hxwm.hwdbtool.entity.dbconnect.HwDateSource;
import com.hxwm.hwdbtool.entity.dbmigration.MapperExcel;
import com.hxwm.hwdbtool.entity.dbmigration.MapperSheet;
import com.hxwm.hwdbtool.entity.dbmigration.MapperTable;
import com.hxwm.hwdbtool.service.ExecuteScriptService;
import com.hxwm.hwdbtool.service.GenerateScriptService;
import com.hxwm.hwdbtool.util.HwFileUtil;
import com.hxwm.hwdbtool.util.HwUtil;
import com.hxwm.hwdbtool.util.JavaFXUIControlUtil;
import javafx.application.Platform;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.scene.Node;
import javafx.scene.Parent;
import javafx.scene.control.*;
import javafx.scene.layout.HBox;
import javafx.stage.DirectoryChooser;
import javafx.stage.FileChooser;
import lombok.Data;
import org.apache.log4j.Logger;

import java.io.File;
import java.io.IOException;
import java.nio.charset.Charset;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @program: hwdbtool
 * @ClassName DbMigrationController
 * @description:
 * @author: hxwm
 * @create: 2024-05-11 20:40
 * @Version 1.0
 **/
@Data
public class DbMigrationController implements DatabaseConnectionListener {
    Logger logger = Logger.getLogger(DbMigrationController.class);
    @FXML
    private SplitPane migrationPane;
    @FXML
    private ComboBox exportFileFormComboBox;
    @FXML
    private ComboBox exportCharacterSetComboBox;
    @FXML
    private Button removeDataSourceBtn;
    @FXML
    private Label removeDataSourceLabel;
    @FXML
    private Button addDBSourceBtn;
    @FXML
    private Button generateMapperFileBtn;
    @FXML
    private Label generateMapperFileLabel;
    @FXML
    private ComboBox dataSourceListComboBox;
    @FXML
    private HBox oldDBSourceHBox;
    @FXML
    private Button chooseMapperFileBtn;
    @FXML
    private TextField mapperFileTextField;
    @FXML
    private Button chooseOutDirBtn;
    @FXML
    private TextField outDirTextField;
    @FXML
    private Button exportBtn;
    @FXML
    private ComboBox importTypeComboBox;
    @FXML
    private CheckBox isSkipErrorCheckBox;
    @FXML
    private Button chooseDBSourceBtn;
    @FXML
    private ComboBox importCharacterSetComboBox;
    @FXML
    private Button chooseImportFileBtn;
    @FXML
    private TextField importFileTextField;
    @FXML
    private Button chooseImportDirBtn;
    @FXML
    private TextField importDirTextField;
    @FXML
    private Button chooseOutDirNewBtn;
    @FXML
    private TextField outDirNewTextField;
    @FXML
    private Button importBtn;

    private HwDateSource newDateSource;
    //数据库源列表
    private ObservableList<String> oldDataSourceOptions;
    private Map<String,HwDateSource> oldDataSourceMap;
    private HwDBToolController parentController;
    private String importType;
    private String listenerType;
    private boolean isSkipErrorCheck;
    private Charset exportCharacterSet ;
    private Charset importCharacterSet ;
    public void initialize() {

        // 初始化操作，可以在这里添加事件监听器等
        oldDataSourceOptions = FXCollections.observableArrayList();
        oldDataSourceMap= new HashMap<>();
        dataSourceListComboBox.setItems(oldDataSourceOptions);

        //导出文件格式
        exportFileFormComboBox.setValue("SQL");
        exportFileFormComboBox.valueProperty().addListener((observable, oldValue, newValue)->{

        });
        //文件编码
        exportCharacterSetComboBox.setValue("UTF-8");
        exportCharacterSet = HwFileUtil.characterSetMapper("UTF-8");
        exportCharacterSetComboBox.valueProperty().addListener((observable, oldValue, newValue)->{
            exportCharacterSet = HwFileUtil.characterSetMapper((String) newValue);
        });
        importCharacterSetComboBox.setValue("UTF-8");
        importCharacterSet = HwFileUtil.characterSetMapper("UTF-8");
        importCharacterSetComboBox.valueProperty().addListener((observable, oldValue, newValue)->{
            importCharacterSet = HwFileUtil.characterSetMapper((String) newValue);
        });

        //添加数据源
        addDBSourceBtn.setOnAction(e->{
            listenerType = "exportListener";
            String value = (String) dataSourceListComboBox.getValue();
            HwDateSource hwDateSource = null;
            if(!HwUtil.isNullOrEmpty(value)) {
                hwDateSource = oldDataSourceMap.get(value.substring(0, value.lastIndexOf("-")));
            }
            showConnectDialog(hwDateSource);
        });
        //选择映射文件
        chooseMapperFileBtn.setOnAction(e->{
            handleChooseMapperFile();
        });
        //选择输出目录
        chooseOutDirBtn.setOnAction(e->{
            handleChooseOutDir();
        });
        // 默认导入方式添加选项
        importTypeComboBox.setValue("单个导入");
        importType = "singleImportOption";

        importTypeComboBox.valueProperty().addListener((observable, oldValue, newValue)->{
            if("批量导入".equals(newValue)){
                chooseImportFileBtn.setDisable(true);
                importFileTextField.setDisable(true);
                chooseImportDirBtn.setDisable(false);
                importDirTextField.setDisable(false);
                isSkipErrorCheckBox.setSelected(true);
                importFileTextField.setText("");
                importType = "batchImportOption";
            }
            if("单个导入".equals(newValue)){
                chooseImportFileBtn.setDisable(false);
                importFileTextField.setDisable(false);
                chooseImportDirBtn.setDisable(true);
                importDirTextField.setDisable(true);
                importDirTextField.setText("");
                importType = "singleImportOption";
            }
        });
        // 创建一个事件监听器
        isSkipErrorCheckBox.selectedProperty().addListener((observable, oldValue, newValue) -> {
            isSkipErrorCheck = newValue;
            if (newValue) {
                chooseOutDirNewBtn.setDisable(false);
                outDirNewTextField.setDisable(false);
            } else {
                chooseOutDirNewBtn.setDisable(true);
                outDirNewTextField.setDisable(true);
                outDirNewTextField.setText("");
            }
        });
        chooseDBSourceBtn.setOnAction(e->{
            listenerType = "importListener";
            showConnectDialog(newDateSource);
        });
        chooseImportFileBtn.setOnAction(e->{
            handleChooseImportFile();
        });
        chooseImportDirBtn.setOnAction(e->{
            handleChoosrImportDir();
        });
        chooseOutDirNewBtn.setOnAction(e->{
            handleChooseOutDirNew();
        });
        removeDataSourceBtn.setOnAction(e->{
            int indexOf = oldDataSourceOptions.indexOf(dataSourceListComboBox.getValue());
            String value = (String) dataSourceListComboBox.getValue();
            if(!HwUtil.isNullOrEmpty(value)) {
                oldDataSourceOptions.remove(value);
                oldDataSourceMap.remove(value.substring(0,value.lastIndexOf("-")));
                if (HwUtil.isNullOrEmpty(oldDataSourceMap)) {
                    oldDBSourceHBox.setDisable(true);
                }
            }
        });
        //导出数据
        exportBtn.setOnAction(e->{
            handleExport();
        });
        //导入数据
        importBtn.setOnAction(e->{
            handleImport(importType);
        });
        generateMapperFileBtn.setOnAction(e->{
            try {
                FXMLLoader fxmlLoader = new FXMLLoader(getClass().getResource("/fxml/dbmigration/generatemapper.fxml"));
                GenerateMapperController controller = fxmlLoader.getController();
                Parent dialogRoot = fxmlLoader.load();

                Dialog<ButtonType> dialog = new Dialog<>();
                dialog.setTitle("创建映射文件");
                dialog.getDialogPane().getButtonTypes().addAll(ButtonType.CANCEL);
                Node sysCancelButton = dialog.getDialogPane().lookupButton(ButtonType.CANCEL);
                sysCancelButton.setVisible(false);

                dialog.getDialogPane().setContent(dialogRoot);
                // 设置Dialog的owner为main.fxml的窗口
                dialog.initOwner(null);
                // 处理窗口关闭事件，点击右上角X按钮时触发
                dialog.setOnCloseRequest(event1 -> {
                    dialog.close();
                });

                dialog.show();

            } catch (IOException ex) {
                throw new RuntimeException(ex);
            }


        });
    }

    private void handleChooseMapperFile(){
        FileChooser fileChooser = new FileChooser();
        String mapperFileText = mapperFileTextField.getText();
        if(!HwUtil.isNullOrEmpty(mapperFileText)){
            mapperFileText = mapperFileText.substring(0,mapperFileText.lastIndexOf("\\"));
            fileChooser.setInitialDirectory(new File(mapperFileText));
        }
        fileChooser.getExtensionFilters().add(new FileChooser.ExtensionFilter("Excel Files", "*.xlsx"));
        File file = fileChooser.showOpenDialog(migrationPane.getScene().getWindow());
        if(file!=null) {
            mapperFileTextField.setText(file.getAbsolutePath());
        }
    }

    private void handleChooseOutDir(){
        DirectoryChooser directoryChooser = new DirectoryChooser();
        String outDirText = outDirTextField.getText();
        String mapperFileText = mapperFileTextField.getText();
        if(!HwUtil.isNullOrEmpty(outDirText)){
            directoryChooser.setInitialDirectory(new File(outDirText));
        }else if(!HwUtil.isNullOrEmpty(mapperFileText)){
            mapperFileText = mapperFileText.substring(0,mapperFileText.lastIndexOf("\\"));
            directoryChooser.setInitialDirectory(new File(mapperFileText));
        }
        File file = directoryChooser.showDialog(migrationPane.getScene().getWindow());
        if(file!=null) {
            outDirTextField.setText(file.getAbsolutePath());
        }
    }
    /**
     * 选择脚本文件
     */
    private void handleChooseImportFile(){
        FileChooser fileChooser = new FileChooser();
        String outDirText = outDirTextField.getText();
        String importFileText = importFileTextField.getText();
        if(!HwUtil.isNullOrEmpty(outDirText)){
            fileChooser.setInitialDirectory(new File(outDirText));
        }else if(!HwUtil.isNullOrEmpty(importFileText)){
            importFileText = importFileText.substring(0, importFileText.lastIndexOf("\\"));
            fileChooser.setInitialDirectory(new File(importFileText));
        }
        fileChooser.getExtensionFilters().add(new FileChooser.ExtensionFilter("SQL Files", "*.sql"));
        File file = fileChooser.showOpenDialog(migrationPane.getScene().getWindow());
        if (file != null) {
            String absolutePath = file.getAbsolutePath();
            importFileTextField.setText(absolutePath);
            importDirTextField.setText("");
            if(isSkipErrorCheck){
                String outPath = absolutePath.substring(0,absolutePath.lastIndexOf("\\")) + File.separator + "errorSql";
                File outFile = new File(outPath);
                if(!outFile.exists()){
                    outFile.mkdirs();
                }
                outDirNewTextField.setText(outPath);
            }
        }
    }

    /**
     * 选择导入目录
     */
    private void handleChoosrImportDir(){
        DirectoryChooser directoryChooser = new DirectoryChooser();
        String outDirText = outDirTextField.getText();
        if(!HwUtil.isNullOrEmpty(outDirText)){
            directoryChooser.setInitialDirectory(new File(outDirText));
        }
        File file = directoryChooser.showDialog(migrationPane.getScene().getWindow());
        if(file!=null) {
            String absolutePath = file.getAbsolutePath();
            importDirTextField.setText(absolutePath);
            if(isSkipErrorCheck){
                String outPath = absolutePath + File.separator + "errorSql";
                File outFile = new File(outPath);
                if(!outFile.exists()){
                    outFile.mkdirs();
                }
                outDirNewTextField.setText(outPath);
            }
            importFileTextField.setText("");
        }
    }

    /**
     * 选额输出目录
     */
    private void handleChooseOutDirNew(){
        DirectoryChooser directoryChooser = new DirectoryChooser();
        String importDirText = importDirTextField.getText();
        if(!HwUtil.isNullOrEmpty(importDirText)){
            directoryChooser.setInitialDirectory(new File(importDirText));
        }
        File file = directoryChooser.showDialog(migrationPane.getScene().getWindow());
        if(file!=null) {
            String absolutePath = file.getAbsolutePath();
            outDirNewTextField.setText(absolutePath);
        }
    }

    /**
     * 数据导出
     */
    private void handleExport(){
        String mapperFilePath = mapperFileTextField.getText();
        String outDirPath = outDirTextField.getText();

        if(HwUtil.isNullOrEmpty(oldDataSourceMap) ){
            JavaFXUIControlUtil.showInformationDialog("错误信息","数据源不能为空");
            return;
        }
        if(HwUtil.isNullOrEmpty(mapperFilePath)){
            JavaFXUIControlUtil.showInformationDialog("错误信息","映射文件不能为空");
            return;
        }
        if(HwUtil.isNullOrEmpty(outDirPath)){
            JavaFXUIControlUtil.showInformationDialog("错误信息","输出目录不能为空");
            return;
        }
        Platform.runLater(()->{
            GenerateScriptService generateScriptService = new GenerateScriptService();
            Map<String,Statement> oldStatementMap = new HashMap<>();
            try {
                //读取映射文件解析新旧表字段
                MapperExcel mapperExcel = new MapperExcel(mapperFilePath);
                List<MapperSheet> mapperSheetList = mapperExcel.getMapperSheetList();
                exportBtn.setDisable(true);
                Map dataSourceNameMap = new HashMap();
                for (MapperSheet mapperSheet : mapperSheetList) {
                    if(mapperSheet.isMultiTable()){
                        logger.info("多数据源导出：");
                        List<MapperTable> oldTableList = mapperSheet.getOldTableList();

                        for (MapperTable mapperTable : oldTableList) {
                            String keyName = mapperTable.getDataSourceKeyName();
                            dataSourceNameMap.put(keyName,"");
                            HwDateSource hwDateSource = oldDataSourceMap.get(keyName);
                            if(HwUtil.isNullOrEmpty(oldStatementMap.get(keyName))) {
                                oldStatementMap.put(keyName, hwDateSource.getConnection().createStatement());

                            }
                        }
                        if(dataSourceNameMap.size() > 1) {
                            //多源
                            logger.info("多数据源导出：");
                            String dataSourceKeyName1 = oldTableList.get(0).getDataSourceKeyName();
                            String dataSourceKeyName2 = oldTableList.get(1).getDataSourceKeyName();

                            generateScriptService.generateMultiSqlFile(oldStatementMap.get(dataSourceKeyName1),oldStatementMap.get(dataSourceKeyName2), mapperSheet, outDirPath,exportCharacterSet);
                        }else{
                            logger.info("单数据源-多对一导出：");
                            String dataSourceKeyName = oldTableList.get(0).getDataSourceKeyName();
                            HwDateSource hwDateSource = oldDataSourceMap.get(dataSourceKeyName);

                            generateScriptService.generateMultiSqlFile(oldStatementMap.get(dataSourceKeyName), mapperSheet, outDirPath,hwDateSource.getDriverType(),exportCharacterSet);
                        }
                    }else{
                        logger.info("单数据源-一对一导出：");
                        String dataSourceKeyName = mapperSheet.getOldTable().getDataSourceKeyName();
                        HwDateSource hwDateSource = oldDataSourceMap.get(dataSourceKeyName);
                        oldStatementMap.put(dataSourceKeyName,hwDateSource.getConnection().createStatement());
                        generateScriptService.generateSqlFile(oldStatementMap.get(dataSourceKeyName),mapperSheet,outDirPath,hwDateSource.getDriverType(),exportCharacterSet);
                    }
                }
            }catch (Exception e){
                logger.error("导出数据错误!" + e.getMessage());
            }finally {
                exportBtn.setDisable(false);
                try {
                    for (Map.Entry<String, Statement> entry : oldStatementMap.entrySet()) {
                        Statement value = entry.getValue();
                        if (value != null) {
                            value.close();
                        }
                    }
                }catch (Exception exception){
                    logger.error(exception.getMessage());
                }
            }
        });
    }
    /**
     * 数据导入
     * @param importType
     */
    private void handleImport(String importType){
        migrationPane.getScene().getWindow();

        ExecuteScriptService executeScriptService = new ExecuteScriptService(parentController.getMigrationLogListView());

        String scriptPath = importFileTextField.getText();
        String inDirPath = importDirTextField.getText();
        String outDirPath = outDirNewTextField.getText();
        if(HwUtil.isNullOrEmpty(newDateSource)){
            JavaFXUIControlUtil.showInformationDialog("提示", "数据源配置不能为空！");
            return;
        }
        if("batchImportOption".equals(importType)){
            if(HwUtil.isNullOrEmpty(inDirPath)){
                JavaFXUIControlUtil.showInformationDialog("提示", "导入目录不能为空！");
                return;
            }
            executeScriptService.executeScriptDir(inDirPath,outDirPath,newDateSource,importCharacterSet,importBtn);
        }
        if("singleImportOption".equals(importType)){
            if(HwUtil.isNullOrEmpty(scriptPath)){
                JavaFXUIControlUtil.showInformationDialog("提示", "脚本文件不能为空！");
                return;
            }
            executeScriptService.executeScript(scriptPath, outDirPath, newDateSource, importCharacterSet,importBtn);
        }
    }
    private void handlegenerateMapperFile(){
        FXMLLoader fxmlLoader = new FXMLLoader(getClass().getResource("/fxml/dbmigration/generatemapper.fxml"));

    }

    /**
     * 数据库连接弹框
     * @param hwDateSource
     */
    private void showConnectDialog(HwDateSource hwDateSource){
        // 处理数据库连接菜单事件
        try {
            FXMLLoader fxmlLoader = new FXMLLoader(getClass().getResource("/fxml/dbconnect/dbconnectdialog.fxml"));
            Parent dialogRoot = fxmlLoader.load();
            //设置监听器，监听对话框的确定按钮，传递数据源对象
            BaseDBConnectDialog dialogController = fxmlLoader.getController();
            dialogController.setListener(this); // 设置主页面控制器为监听器

            Dialog<ButtonType> dialog = new Dialog<>();
            dialog.setTitle("新建数据库连接");
            dialog.getDialogPane().getButtonTypes().addAll(ButtonType.CANCEL);
            Node sysCancelButton = dialog.getDialogPane().lookupButton(ButtonType.CANCEL);
            sysCancelButton.setVisible(false);

            dialog.getDialogPane().setContent(dialogRoot);
            // 设置Dialog的owner为main.fxml的窗口
            dialog.initOwner(migrationPane.getScene().getWindow());
            // 处理窗口关闭事件，点击右上角X按钮时触发
            dialog.setOnCloseRequest(event1 -> {
                dialog.close();
            });

            if(!HwUtil.isNullOrEmpty(hwDateSource)){
                dialogController.getDbIpTextField().setText(hwDateSource.getDbIp());
                dialogController.getDbPortTextField().setText(hwDateSource.getDbPort());
                dialogController.getDbTypeComboBox().setValue(hwDateSource.getDriverType());
                dialogController.getDbNameTextField().setText(hwDateSource.getDbName());
                dialogController.getDbUserTextField().setText(hwDateSource.getDbUser());
                dialogController.getDbPasswordField().setText(hwDateSource.getDbPassword());
            }
            dialog.show();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void onDatabaseConnected(HwDateSource hwDateSource) {

        if(!HwUtil.isNullOrEmpty(hwDateSource)){
            logger.info("添加数据源");
            if ("exportListener".equals(listenerType)){
                logger.info("添加导出数据源");
                String dbName = hwDateSource.getDbName();
                String dbIp = hwDateSource.getDbIp();
                String dbUser = hwDateSource.getDbUser();
                String keyName = dbName + "-" + dbUser + "-" + dbIp;
                //数据库添加了dbname,此时判断是否已添加了数据源
                int indexOf = oldDataSourceOptions.indexOf(keyName);
                if(indexOf < 0){
                    oldDataSourceOptions.add(keyName);
                    oldDataSourceMap.put(dbName + "-" + dbUser,hwDateSource);
                }
            }
            if ("importListener".equals(listenerType)){
                newDateSource = hwDateSource;
            }
            logger.info("添加数据源成功");
        }
        if(!HwUtil.isNullOrEmpty(oldDataSourceMap)){
            oldDBSourceHBox.setDisable(false);
        }
    }

}